<?php

require_once ('config.php');

/***************************************************************
  Initialization
 **************************************************************/

echo '<pre><h1>Initialization</h1>';

echo 'Connect to database.<br>';
$db = new DaBase_Db('localhost', 'root', '', 'socnet');

echo 'Init tables with some data.<br>';
foreach (explode(';', file_get_contents('tables.sql')) as $sql) {
	if(trim($sql, " \n\r")) {
		$db->exec($sql);
	}
}

// debug
function debugSql($sql) {
	echo '<strong><font color="blue">' . $sql . '</font></strong><br>';
}
$db->setDebugCallback('debugSql');

/***************************************************************
  DaBase_Db query preparing and fetching
 **************************************************************/

echo '<h1>DaBase_Db query preparing and fetching</h1>';

echo 'Query preparing example:<br>';
$isActive = true;
$sets = array('isModerator' => true, 'isRoot' => false);
$usersIds = array(1, 2, 3);

$result = $db->query("UPDATE `users` SET ,= WHERE `id` IN (,?) AND `isActive`=?", $sets, $usersIds, $isActive);

echo '<br>Query result fetching examples:<br><br>';
echo '$db->fetch<br>';
$rows = $db->fetch('SELECT * FROM `users` LIMIT 2'); // to get result as array of all rows and columns
print_r($rows);

echo '<br>';
echo '$db->fetchRow <br>';
$row = $db->fetchRow('SELECT * FROM `users` LIMIT 1'); // to get associative array of first row
print_r($row);

echo '<br>';
echo '$db->fetchColumn <br>';
$column = $db->fetchColumn('SELECT `id` FROM `users`'); // to get array of first column of all rows
print_r($column);

echo '<br>';
echo '$db->fetchCell <br>';
$cell = $db->fetchCell('SELECT COUNT(*) FROM `users`'); // to get array of first column of all rows
print_r($cell);

/***************************************************************
  DaBase_Getter basic features
 **************************************************************/

echo '<h1>DaBase_Getter basic features</h1>';
echo '<h2>Selectors</h1>';

echo '$db->users->get() <br>';
$users = $db->users->get(); // get array of all rows like objects
print_r($users);

echo '<br>';
echo '$db->users->getByQuery(...) <br>';
$users = $db->users->getByQuery('SELECT * FROM # WHERE `isActive`=? LIMIT 2', $db->users->getTable(), $isActive); // get array of all rows like objects
print_r($users);

echo '<br>';
echo '$db->users->getColumn(\'login\') <br>';
$usersIds = $db->users->getColumn('login'); // get associative array of id => property 
print_r($usersIds);

echo '<br>';
echo '$db->users->count() <br>';
$usersCount = $db->users->count(); // get count of all rows in table `users`
print_r($usersCount);

echo '<br><br>';
echo '$db->users->getObjectById(3) <br>';
$user = $db->users->getObjectById(3); // get one object by id
print_r($user);

echo '<br>';
echo '$db->users(3) <br>';
$user = $db->users(3); // get one object by id
print_r($user);

echo '<h2>Filters</h1>';

echo '$db->users->isActive(true)->isModerator(true)->posts(50, \'>\')->get() <br>';
$users = $db->users->isActive(true)->isModerator(true)->posts(50, '>')->get(); // get users with `isActive`='1' AND `is_moderator`='1' AND `posts` > '50'
print_r($users);

echo '<h2>Orders and limits</h2>';

echo '$db->users->order(\'posts\', true)->order(\'login\')->get()<br>';
$users = $db->users->order('posts', true)->order('login')->get(); // get all users with ORDER BY `posts` DESC, `login`

echo '<br>$db->users->order(\'id\')->limit(5, 20)->get()<br>';
$users = $db->users->order('id')->limit(5, 20)->get(); // get users with ORDER BY `id` LIMIT 5, 20

echo '<br>$db->users->limitPage(10, 3)->get()<br>';
$users = $db->users->limitPage(10, 3)->get(); // get users with LIMIT 10, 20

echo '<br>$db->users->orderRand()->limit(5)->get()<br>';
$users = $db->users->orderRand()->limit(5)->get(); // get 5 random users

echo '<br>$db->users->limit(1)->get(true)<br>';
$user = $db->users->limit(1)->get(true); // ...->get(true) means getting just on object (not array of objects)

echo '<h2>Update</h2>';
echo '$db->users->posts(50, \'>\')->limit(5)->update(array(\'isModerator\' => true))<br>';
$db->users->posts(50, '>')->limit(5)->update(array('isModerator' => true)); // UPDATE `users` SET `isModerator`='1' WHERE `posts`<'50' LIMIT 5

echo '<h2>Delete</h2>';
echo '$db->users->login(\'sergey\')->delete()<br>';
$db->users->login('sergey')->delete(); // DELETE FROM `users` WHERE `login`='sergey'


/***************************************************************
  DaBase_Getter appenders(pseudo-JOINS)
 **************************************************************/

echo '<h1>DaBase_Getter appenders(pseudo-JOINS)</h1>';

echo '$db->users->order(\'login\')->limit(2)
->append($db->videos->order(\'id\'))
->append($db->photos->order(\'id\')
  ->append($db->photosComments))
->get()<br>';
$users = $db->users->order('login')->limit(2)
->append($db->videos->order('id'))
->append($db->photos->order('id')
  ->append($db->photosComments))
->get();
print_r($users);

echo '<h2>Appending by custom properties names</h2>';

echo '$db->users
->append($db->videos->orderRand(), \'randomVideos\', \'userId\')
->get()<br>';
$users = $db->users
->append($db->videos->orderRand(), 'randomVideos', 'userId')
->get();
print_r($users);


/***************************************************************
  Custom getters extended from DaBase_Getter 
 **************************************************************/

echo '<h2>Custom getters extended from DaBase_Getter</h1>';

echo '$db->rootUsers->get()<br>';
$users = $db->rootUsers->get();
print_r($users);

/***************************************************************
  Database objects definition by class models with autovalidation
 **************************************************************/

echo '<h1>Database objects definition by class models with autovalidation (DaBase_Object, Validator)</h1>';

echo '<h2>Insert</h2>';
?>
$user = $db->users->getNew(); // get clear object
$user->login = 'johny';
$user->password = md5('jdskalkjaslkd');
$user->insert();
<?php 
$user = $db->users->getNew(); // get clear object
$user->login = 'johny';
$user->password = md5('jdskalkjaslkd');
$user->insert();
print_r($user);

?>

$db->users->getNew()->login('patrick')->passwod(md5('dsaa'))->isRoot(true)->insert();
<?php 
$user = $db->users->getNew()->login('patrick')->password(md5('dsaa'))->isRoot(true)->insert();
print_r($user);

echo '<h2>Update</h2>';
?>
$db->users->login('johny')->get(true)->isRoot(true)->isModerator(true)->update();
<?php 
$user = $db->users->login('johny')->get(true)->isRoot(true)->isModerator(true)->update();
print_r($user);

echo '<h2>Delete</h2>';
?>
$db->users->login('johny')->get(true)->delete();
<?php 
$user = $db->users->login('johny')->get(true)->delete();
print_r($user);

echo '<h1>Data models based on DaBase_Object</h1>';

?>
class User extends DaBase_Object {
	
	public $login;
	public $password;
	public $posts;
	public $isModerator;
	public $isRoot;

	const validateOnInsert = true;
	const validateOnUpdate = 'login,email';

	protected function getValidator() {
		$validator = new Validator_Set();
		
		$validator->add('login', array(
		new Validator_Rule_Required(), 
		new Validator_Rule_Length(3, 20), 
		new Validator_Rule_Regexp('/^[a-z\d]*$/ui')));
		
		$validator->add('password', array(
		new Validator_Rule_Required(), 
		new Validator_Rule_Regexp('/^[a-z\d]*$/ui'), 
		new Validator_Rule_Length(6, 50)));
		
		return $validator;
	}
}
<?php 

echo '<h2>DaBase_Object validation</h2>';
?>
try {
  $db->users->getNew()->login('mike')->insert();
}
catch(Validator_Exception $e) {
  print_r($e->getErrors());
}
<?php 

try {
	$db->users->getNew()->login('mike')->insert();
}
catch(Validator_Exception $e) {
	print_r($e->getErrors());
}

echo '<h2>DaBase_Object custom validation without exception throwing</h2>';
?> 
$newUser = $db->users->getNew();
$user->login = 'me';
if(!$user->validate('login,password', false)) {
  print_r($user->getValidationErrors());
}
<?php 
$user = $db->users->getNew();
$user->login = 'me';
if(!$user->validate('login,password', false)) {
  print_r($user->getValidationErrors());
}
